/********************************************************************************************************

Script:			Add Operators
Author:			Richard Slim
Date:			12/11/2012
Description:	This script adds the operator if it doesn't exist and assigns it to each created job

Usage:			This is called from within the main SSIS task but can also be used for manually
				
********************************************************************************************************/

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysoperators WHERE name = 'SQL.DBA')
BEGIN
	EXEC msdb.dbo.sp_add_operator @name=N'SQL.DBA', 
		@enabled=1, 
		@pager_days=0, 
		@email_address=?
END

EXEC msdb..sp_update_job @job_name = 'DBM - DatabaseBackup - SYSTEM_DATABASES - FULL', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - DatabaseBackup - USER_DATABASES - DIFF', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - DatabaseBackup - USER_DATABASES - FULL', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - DatabaseBackup - USER_DATABASES - LOG', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - DatabaseIntegrityCheck - SYSTEM_DATABASES', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - DatabaseIntegrityCheck - USER_DATABASES', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - IndexOptimize - USER_DATABASES', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - CommandLog Cleanup', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - Output File Cleanup', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - sp_delete_backuphistory', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'
EXEC msdb..sp_update_job @job_name = 'DBM - sp_purge_jobhistory', @notify_level_email=2, @notify_email_operator_name=N'SQL.DBA'